local dir `c(pwd)'

********************************************************************************
* CALCULATE REPURCHASE-ADJUSTED DIVIDENDS
********************************************************************************
* Follow Bansal, Dittmar, and Lundblad (2005)

* Download the CRSP-->Stock/Security Files-->Monthly Stock File as a Stata data file
use "../Data/CRSP.dta"

* Only use common shares and stocks traded on NYSE, AMEX, and NASDAQ
drop if shrcd != 10 & shrcd != 11
keep if exchcd == 1 | exchcd ==2 | exchcd == 3

gen yyyy = year(date)
gen mm= month(date)

* Fill in missing time series observations
* Otherwise wrong to use lagged values (not from last mm)
gen date_2 = ym(yyyy,mm) 
sort permno yyyy mm date
tsset permno date_2
tsfill

replace yyyy = 1960 + floor(date_2/12) if missing(yyyy)
replace mm = 1 + mod(date_2 , 12) if missing(mm)

* Construct measures
replace prc = abs(prc)
gen mcap = shrout * prc
gen shroutadj = shrout * cfacshr

sort permno date_2
by permno: gen lshroutadj = shroutadj[_n - 1]
by permno: gen lmcap = mcap[_n - 1]

* Calculate adjusted dividend
gen sfactor = shroutadj/lshroutadj
replace sfactor = 1 if sfactor < 0.9 /* Drop observations where the adjusted shares outstanding fall by more than 10 percent within one month*/
gen retxadj = (1 + retx) * min(sfactor , 1) - 1
gen divadj = lmcap * (ret - retxadj)
gen div = lmcap * (ret - retx)

* Get aggregate adjusted dividend payout
sort yyyy mm permno
by yyyy mm: egen D = total(divadj)
by yyyy mm: egen mcaptot = total(mcap)

* Get values
drop if missing(date)
keep yyyy mm D mcaptot 
duplicates drop
drop if yyyy == 1925

* Export to Excel
export excel using "D_CRSP", firstrow(variables) replace

********************************************************************************
* WITHOUT ANY ADJUSTMENT
********************************************************************************
* Download the CRSP-->Stock/Security Files-->Stock Market Indexes at monthly frequency as a Stata data file
use "../Data/CRSPIndex.dta", clear

gen yyyy = year(date)
gen mm = month(date)

sort date
gen ltotval = totval[_n-1]
gen D_idx = ltotval * (vwretd - vwretx)

keep yyyy mm vwretd vwretx totval D_idx

* Export to Excel
export excel using "D_idx_CRSP", firstrow(variables) replace

********************************************************************************
* CALCULATE RETURN VOLATILITY
********************************************************************************
* Download the CRSP-->Stock/Security Files-->Stock Market Indexes at daily frequency as a Stata data file
use "../Data/CRSP_Daily.dta", clear

gen yyyy = year(date)
gen mm = month(date)
gen qtr = quarter(date)

sort date
drop if yyyy > 2019

sort yyyy qtr

* Using realized daily log returns
gen ret = log(1 + vwretd)
by yyyy qtr: egen ret_mean = mean(ret)
gen ret_dm_s = (ret - ret_mean)^2
gen ret_s = ret^2

by yyyy qtr: egen retvar = total(ret_s), missing
gen retvol = sqrt(retvar)
label var retvol "Volatility using log returns"

keep yyyy qtr retvol 
duplicates drop

* Export to Excel
drop if yyyy == 1925
export excel using "retvol_CRSP", firstrow(variables) keepcellfmt replace
